package com.bioroadlab.web;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.bioroadlab.entity.Disease;
import com.bioroadlab.util.HssfExcel;
import com.bioroadlab.util.StringUtil;
import com.mysql.jdbc.Connection;

public class CaptureServlet extends HttpServlet {

	private static final long serialVersionUID = 1L;
	private static File file = new File("G:\\java测试\\streamExcel.xls");
	
	private static List<String> listUrl = null;
	private static String urlStr;
	private static int i;

	private static String s1;
	private static String s2;
	private static String s3;
	private static String s4;
	private static String s5;
	private static String s6;
	private static String s7;
	private static String s8;
	private static String s9;
	private static String s10;
	private static String s11;
	private static String s12;
	private static String s13;
	private static String s14;
	private static String s15;
	private static String s16;
	private static String s17;
	private static String sWriter;
	
	private static String firstLink;
	
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		String task = req.getParameter("task");
		if(task.equals("captureStart")){
			toCaptureData(req,resp);
		}
	}

	private void toCaptureData(HttpServletRequest req, HttpServletResponse resp) {
		
		writeTitleLink();
		
		toInLink();
		
		Connection conn = null;
		Statement stat = null;
		try {
			/**
			 * 进入数据库查找最后一个下一页的一个字符串链接，进行判断
			 */
			Class.forName("com.mysql.jdbc.Driver");
			conn = (Connection) DriverManager.getConnection("jdbc:mysql:///captures", "root", "root");
			stat = (Statement) conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from t_nextlink order by id DESC limit 1");
			/**
			 * 如果没有的话
			 */
			if (rs.getRow() < 0) {
				Class.forName("com.mysql.jdbc.Driver");
				conn = (Connection) DriverManager.getConnection("jdbc:mysql:///diseaseoflinkcapture", "root", "root");
				stat = (Statement) conn.createStatement();
				ResultSet rs1 = stat.executeQuery("select * from t_diseaselink order by id DESC limit 1");
				while (rs1.next()) {
					String firstLinkName = rs.getString("firstLinkName");
					String diseaseLinkName = rs.getString("diseaseLinkName");
					System.out.println("sn:" + firstLinkName + "\tname:" + diseaseLinkName);
				}
			} else {
				while (rs.next()) {
					String firstLinkName = rs.getString("firstLinkName");
					String nextLinkName = rs.getString("nextLinkName");
					Class.forName("com.mysql.jdbc.Driver");
					conn = (Connection) DriverManager.getConnection("jdbc:mysql:///diseaseoflinkcapture", "root","root");
					stat = (Statement) conn.createStatement();
					ResultSet rs1 = stat.executeQuery("select * from t_diseaselink order by id DESC limit 1");
					String firstLinkName1 = "";
					String diseaseLinkName1 = "";
					while (rs1.next()) {// diseaselink
						firstLinkName1 = rs1.getString("firstLinkName");
						diseaseLinkName1 = rs1.getString("diseaseLinkName");
						System.out.println("sn:" + firstLinkName1 + "\tname:" + diseaseLinkName1);
					}
					if (firstLinkName.equals(firstLinkName1)) {// 一般情况
						normalReadPage(firstLinkName, nextLinkName);
					}
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	private void toInLink() {
		Connection conn = null;
		Statement stat = null;
		try {
			/**
			 * 进入数据库查找最后一个下一页的一个字符串链接，进行判断
			 */
			Class.forName("com.mysql.jdbc.Driver");
			conn = (Connection) DriverManager.getConnection("jdbc:mysql:///captures", "root", "root");
			stat = (Statement) conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from t_nextlink order by id DESC limit 1");
			/**
			 * 如果没有的话,
			 */
			if (rs.getRow() < 0) {
				Class.forName("com.mysql.jdbc.Driver");
				conn = (Connection) DriverManager.getConnection("jdbc:mysql:///diseaseoflinkcapture", "root", "root");
				stat = (Statement) conn.createStatement();
				ResultSet rs1 = stat.executeQuery("select * from t_diseaselink order by id DESC limit 1");
				while (rs1.next()) {
					String firstLinkName = rs.getString("firstLinkName");
					String diseaseLinkName = rs.getString("diseaseLinkName");
					System.out.println("sn:" + firstLinkName + "\tname:" + diseaseLinkName);
				}
			} else {
				while (rs.next()) {
					String firstLinkName = rs.getString("firstLinkName");
					String nextLinkName = rs.getString("nextLinkName");
					Class.forName("com.mysql.jdbc.Driver");
					conn = (Connection) DriverManager.getConnection("jdbc:mysql:///diseaseoflinkcapture", "root","root");
					stat = (Statement) conn.createStatement();
					ResultSet rs1 = stat.executeQuery("select * from t_diseaselink order by id DESC limit 1");
					String firstLinkName1 = "";
					String diseaseLinkName1 = "";
					while (rs1.next()) {// diseaselink
						firstLinkName1 = rs1.getString("firstLinkName");
						diseaseLinkName1 = rs1.getString("diseaseLinkName");
						System.out.println("sn:" + firstLinkName1 + "\tname:" + diseaseLinkName1);
					}
					if (firstLinkName.equals(firstLinkName1)) {// 一般情况
						normalReadPage(firstLinkName, nextLinkName);
					}
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static void writeTitleLink() {
		try {
				/**
				 * 进入第一页面链接,http://www.omim.org/statistics/entry
				 */
				URL url = new URL("http://www.omim.org/statistics/entry".toString());
				HttpURLConnection httpConn = (HttpURLConnection) url.openConnection();
				InputStreamReader input = new InputStreamReader(httpConn.getInputStream(), "UTF-8");
				BufferedReader buffReader = new BufferedReader(input);
				String str = "";
				while ((str = buffReader.readLine()) != null) {
					if (!str.trim().equals("")) {
						/**
						 * 带*号的，即基因的数据的信息进行抓取，表格第一行
						 */
						if (str.contains("<td class=\"statistics-label value-border\"><span class=\"prefix\">*</span>")) {
							//第一个链接表格中第一行、第一列
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							str = str.replaceAll(" ", "%20");
							System.out.println(str);
							String filePath1 = "G:\\数据抓取\\基因*\\gene1.xlsx";
							HssfExcel.createGeneExcel(filePath1);
							//进入第一个链接
							readPage(str, str.toString());
							
							//第二个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath2 = "G:\\数据抓取\\基因*\\gene2.xlsx";
							HssfExcel.createGeneExcel(filePath2);
							System.out.println(str);
							readPage(str, str.toString());
							
							//第三个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath3 = "G:\\数据抓取\\基因*\\gene3.xlsx";
							HssfExcel.createGeneExcel(filePath3);
							System.out.println(str);
							readPage(str, str.toString());
							
							//第四个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath4 = "G:\\数据抓取\\基因*\\gene4.xlsx";
							HssfExcel.createGeneExcel(filePath4);
							System.out.println(str);
							readPage(str, str.toString());
							
							//总数的链接
							str = buffReader.readLine();
							str = "http://www.omim.org"+ str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath5 = "G:\\数据抓取\\基因*\\gene5.xlsx";
							HssfExcel.createGeneExcel(filePath5);
							readPage(str, str.toString());
						}else if(str.contains("<td class=\"statistics-label value-border\"><span class=\"prefix\">+</span>")){
							/**
							 * 带+号的四个链接,进行数据的抓取及存库
							 * 表格第二行
							 */
							//第一个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							str = str.replaceAll(" ", "%20");
							String filePath1 = "G:\\数据抓取\\基因和表型+\\disease1.xlsx";
							HssfExcel.createGeneExcel(filePath1);
							readPage(str, str.toString());
							//第二个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath2 = "G:\\数据抓取\\基因和表型+\\gene2.xlsx";
							HssfExcel.createGeneExcel(filePath2);
							readPage(str, str.toString());
							
							//第三个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath3 = "G:\\数据抓取\\基因和表型+\\gene3.xlsx";
							HssfExcel.createGeneExcel(filePath3);
							readPage(str, str.toString());
							
							//第四个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath4 = "G:\\数据抓取\\基因和表型+\\gene4.xlsx";
							HssfExcel.createGeneExcel(filePath4);
							readPage(str, str.toString());
							
							//总数的链接
							str = buffReader.readLine();
							str = "http://www.omim.org"+ str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath5 = "G:\\数据抓取\\基因和表型+\\gene5.xlsx";
							HssfExcel.createGeneExcel(filePath5);
							readPage(str, str.toString());
						} else if(str.contains("<td class=\"statistics-label value-border\"><span class=\"prefix\">#</span>")){
							/**
							 * 带#号的四个链接,进行数据的抓取及存库
							 * 表格第三行
							 */
							//第一个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							str = str.replaceAll(" ", "%20");
							String filePath1 = "G:\\数据抓取\\表型#\\disease1.xlsx";
							HssfExcel.createDiseaseExcel(filePath1);
							readPage(str, str.toString());
							//第二个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath2 = "G:\\数据抓取\\表型#\\gene2.xlsx";
							HssfExcel.createDiseaseExcel(filePath2);
							readPage(str, str.toString());
							
							//第三个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath3 = "G:\\数据抓取\\表型#\\gene3.xlsx";
							HssfExcel.createDiseaseExcel(filePath3);
							readPage(str, str.toString());
							
							//第四个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath4 = "G:\\数据抓取\\表型#\\gene4.xlsx";
							HssfExcel.createDiseaseExcel(filePath4);
							readPage(str, str.toString());
							
							//总数的链接
							str = buffReader.readLine();
							str = "http://www.omim.org"+ str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath5 = "G:\\数据抓取\\表型#\\gene5.xlsx";
							HssfExcel.createDiseaseExcel(filePath5);
							readPage(str, str.toString());
						} else if(str.contains("<td class=\"statistics-label value-border\"><span class=\"prefix\">%</span>")){
							/**
							 * 带%号的四个链接,进行数据的抓取及存库
							 * 表格第四行
							 */
							//第一个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							str = str.replaceAll(" ", "%20");
							String filePath1 = "G:\\数据抓取\\表型描述或位点%\\disease1.xlsx";
							HssfExcel.createDiseaseExcel(filePath1);
							readPage(str, str.toString());
							//第二个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath2 = "G:\\数据抓取\\表型描述或位点%\\gene2.xlsx";
							HssfExcel.createDiseaseExcel(filePath2);
							readPage(str, str.toString());
							
							//第三个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath3 = "G:\\数据抓取\\表型描述或位点%\\gene3.xlsx";
							HssfExcel.createDiseaseExcel(filePath3);
							readPage(str, str.toString());
							
							//第四个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath4 = "G:\\数据抓取\\表型描述或位点%\\gene4.xlsx";
							HssfExcel.createDiseaseExcel(filePath4);
							readPage(str, str.toString());
							
							//总数的链接
							str = buffReader.readLine();
							str = "http://www.omim.org"+ str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath5 = "G:\\数据抓取\\表性描述或位点%\\gene5.xlsx";
							HssfExcel.createDiseaseExcel(filePath5);
							readPage(str, str.toString());
						}else if(str.contains("<td class=\"statistics-label value-border\"><span class=\"prefix\">&nbsp;</span>")){
							/**
							 * omim号前没有任何符号的号的四个链接,进行数据的抓取及存库
							 * 表格第五行
							 */
							//第一个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							str = str.replaceAll(" ", "%20");
							String filePath1 = "G:\\数据抓取\\其他表型\\disease1.xlsx";
							HssfExcel.createDiseaseExcel(filePath1);
							readPage(str, str.toString());
							//第二个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath2 = "G:\\数据抓取\\其他表型\\gene2.xlsx";
							HssfExcel.createDiseaseExcel(filePath2);
							readPage(str, str.toString());
							
							//第三个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath3 = "G:\\数据抓取\\其他表型\\gene3.xlsx";
							HssfExcel.createDiseaseExcel(filePath3);
							readPage(str, str.toString());
							
							//第四个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath4 = "G:\\数据抓取\\其他表型\\gene4.xlsx";
							HssfExcel.createDiseaseExcel(filePath4);
							readPage(str, str.toString());
							
							//总数的链接
							str = buffReader.readLine();
							str = "http://www.omim.org"+ str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath5 = "G:\\数据抓取\\其他表型\\gene5.xlsx";
							HssfExcel.createDiseaseExcel(filePath5);
							readPage(str, str.toString());
						}else if(str.contains("<td class=\"statistics-label value-border-right\">&nbsp;&nbsp;&nbsp;Totals</td>")){
							/**
							 * 总结total，进行数据的抓取及存库
							 * 表格第六行
							 */
							/*//第一个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							str = str.replaceAll(" ", "%20");
							String filePath1 = "G:\\数据抓取\\total\\disease1.xlsx";
							HssfExcel.createGeneExcel(filePath1);
							readPage(str, str.toString());
							//第二个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath2 = "G:\\数据抓取\\total\\gene2.xlsx";
							HssfExcel.createGeneExcel(filePath2);
							readPage(str, str.toString());
							
							//第三个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath3 = "G:\\数据抓取\\total\\gene3.xlsx";
							HssfExcel.createGeneExcel(filePath3);
							readPage(str, str.toString());
							
							//第四个链接
							str = buffReader.readLine();
							str = "http://www.omim.org" + str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath4 = "G:\\数据抓取\\total\\gene4.xlsx";
							HssfExcel.createGeneExcel(filePath4);
							readPage(str, str.toString());
							
							//总数的链接
							str = buffReader.readLine();
							str = "http://www.omim.org"+ str.substring(str.indexOf("\"/") + 1, str.lastIndexOf("\">"));
							String filePath5 = "G:\\数据抓取\\total\\gene5.xlsx";
							HssfExcel.createGeneExcel(filePath5);
							readPage(str, str.toString());*/
						}
					}
				}
				buffReader.close();

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/*private static void writeTitle() {
		try {
			if (!file.exists()) {
				HSSFWorkbook workBook1 = new HSSFWorkbook();
				HSSFSheet sheet1 = workBook1.createSheet("疾病记录");
				HSSFRow titleRow1 = sheet1.createRow(0);
				HSSFCell[] cellArray = new HSSFCell[17];
				for (int i = 0; i < cellArray.length; i++) {
					cellArray[i] = titleRow1.createCell(i);
				}
				cellArray[0].setCellValue("表型OMIM号");
				cellArray[1].setCellValue("疾病名称");
				cellArray[2].setCellValue("基因OMIM号");
				cellArray[3].setCellValue("致病基因");
				cellArray[4].setCellValue("Phenotypic Series");
				cellArray[5].setCellValue("基因别名");
				cellArray[6].setCellValue("染色体位置");
				cellArray[7].setCellValue("TEXT");
				cellArray[8].setCellValue("Clinical Features");
				cellArray[9].setCellValue("Inheritance");
				cellArray[10].setCellValue("Mapping");
				cellArray[11].setCellValue("Population Genetics");
				cellArray[12].setCellValue("Molecular Genetics");
				cellArray[13].setCellValue("REFERENCES");
				cellArray[14].setCellValue("Contributors");
				cellArray[15].setCellValue("Creation Date");
				cellArray[16].setCellValue("Edit History");
				FileOutputStream out = new FileOutputStream(file, true);
				workBook1.write(out);
				out.flush();
				out.close();
				URL url = new URL("http://www.omim.org/statistics/entry".toString());
				HttpURLConnection httpConn = (HttpURLConnection) url.openConnection();
				InputStreamReader input = new InputStreamReader(httpConn.getInputStream(), "UTF-8");
				BufferedReader buffReader = new BufferedReader(input);
				String str = "";
				while ((str = buffReader.readLine()) != null) {
					if (!str.trim().equals("")) {
						if (str.contains("<td class=\"statistics-label")) {
							if (str.contains("<a href=\"/search")) {
								str = "http://www.omim.org"+ str.substring(str.indexOf("\"/") + 1,str.lastIndexOf("\">"));
								str = str.replaceAll(" ", "%20");
								firstLink = str;
								readPage(str, str.toString());
							}
						}
					}
				}
				buffReader.close();
				FileInputStream isr = new FileInputStream(file);
				byte[] buffer = new byte[9136];
				File file = new File("G:\\数据的抓取\\streamExcel.xls");
				if (!file.exists()) {
					file.createNewFile();
				}
				FileOutputStream fos = new FileOutputStream(file);
				int readnum = 0;
				while ((readnum = isr.read(buffer)) != -1) {
					fos.write(buffer, 0, readnum);
				}
				fos.close();
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}*/
	
	public static void normalReadPage(String firstStr, String urlStr) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			URL url = new URL(urlStr.toString());
			HttpURLConnection httpConn = (HttpURLConnection) url.openConnection();
			InputStreamReader input = new InputStreamReader(httpConn.getInputStream(), "UTF-8");
			BufferedReader buffReaderRead = new BufferedReader(input);
			String str1 = "";
			listUrl = new ArrayList<String>();
			listUrl.add("");
			while ((str1 = buffReaderRead.readLine().toString()) != null) {
				if (!(str1.trim().equals(""))) {
					if (str1.contains("<a href=\"/entry/")) {
						str1 = "http://www.omim.org" + str1.substring(str1.indexOf("\"/") + 1, str1.indexOf("\">"));
						str1 = str1.replaceAll(" ", "%20");
						List<String> list = StringUtil.findTenDisease();
						if (!list.contains(str1)) {
							findEndPage(str1);
							Class.forName("com.mysql.jdbc.Driver");
							conn = (Connection) DriverManager.getConnection("jdbc:mysql:///diseaseoflinkcapture","root", "root");
							stat = (Statement) conn.createStatement();
							stat.execute("INSERT INTO t_diseaselink(firstLinkName,diseaseLinkName) VALUE('"+ firstStr + "','" + str1 + "')");
						} 
					} else if (str1.toString().contains("\">Next")) {
						str1 = "http://www.omim.org" + str1.substring(str1.indexOf("\"/") + 1, str1.indexOf(">Next") - 1);
						str1 = str1.replaceAll(" ", "%20");
						if (listUrl != null) {
							if (listUrl.contains(str1.toString())) {
								listUrl.add(str1);
								Class.forName("com.mysql.jdbc.Driver");
								conn = (Connection) DriverManager.getConnection("jdbc:mysql:///diseaseoflinkcapture","root", "root");
								stat = (Statement) conn.createStatement();
								stat.execute("INSERT INTO t_nextlink(firstLinkName,nextLinkName) VALUE('"+ firstStr + "','" + str1 + "')");
								readPage(firstStr, str1);
							} else {
								listUrl.add(str1);
							}
						}
					}
				}
			}
			buffReaderRead.close();
		} catch (MalformedURLException e) {
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}  finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (stat != null) {
						stat.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						if (conn != null) {
							conn.close();
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}
	
	/**
	 * 疾病的页面信息，进行截字符串存数据库导出excel表
	 * @param strEndUrl
	 */
	public static void readPage(String firstStr, String urlStr) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			URL url = new URL(urlStr.toString());
			HttpURLConnection httpConn = (HttpURLConnection) url.openConnection();
			InputStreamReader input = new InputStreamReader(httpConn.getInputStream(), "UTF-8");
			BufferedReader buffReaderRead = new BufferedReader(input);
			String str1 = "";
			listUrl = new ArrayList<String>();
			listUrl.add("");
			while ((str1 = buffReaderRead.readLine()) != null) {
				if (!(str1.trim().equals(""))) {
					if (str1.contains("<a href=\"/entry/")) {// 得到疾病的链接
						str1 = "http://www.omim.org"+ str1.substring(str1.indexOf("\"/") + 1,str1.indexOf("\">"));
						str1 = str1.replaceAll(" ", "%20");
						System.out.println("\t第二层疾病链接：" + str1);
						findEndPage(str1);
						// "疾病"的链接存数据库
						Class.forName("com.mysql.jdbc.Driver");
						conn = (Connection) DriverManager.getConnection("jdbc:mysql:///diseaseoflinkcapture", "root","root");
						stat = (Statement) conn.createStatement();
						stat.execute("INSERT INTO t_diseaselink(firstLinkName,diseaseLinkName) VALUE('"+ firstStr + "','" + str1 + "')");

					} else if (str1.toString().contains("\">Next")) {// 下页的链接
						str1 = "http://www.omim.org"+ str1.substring(str1.indexOf("\"/") + 1,str1.indexOf(">Next") - 1);
						str1 = str1.replaceAll(" ", "%20");
						if (listUrl != null) {
							if (listUrl.contains(str1.toString())) {
								System.out.println("'下页'链接-------存在了");
								listUrl.add(str1);
								Class.forName("com.mysql.jdbc.Driver");
								conn = (Connection) DriverManager.getConnection("jdbc:mysql:///diseaseoflinkcapture","root", "root");
								stat = (Statement) conn.createStatement();
								stat.execute("INSERT INTO t_nextlink(firstLinkName,nextLinkName) VALUE('"+ firstStr + "','" + str1 + "')");
								readPage(firstStr, str1);
							} else {
								listUrl.add(str1);
								System.out.println("\t第二层下页链接：" + str1);
							}
						}
					}
				}
			}
			buffReaderRead.close();
		} catch (MalformedURLException e) {
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (stat != null) {
						stat.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						if (conn != null) {
							conn.close();
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}

	public static void findEndPage(String strEndUrl) {
		Connection conn = null;
		Statement stat = null;
		
		try {
			URL url = new URL(strEndUrl.toString());
			HttpURLConnection httpConn = (HttpURLConnection) url.openConnection();
			InputStreamReader input = new InputStreamReader(httpConn.getInputStream(), "UTF-8");
			BufferedReader buffReader = new BufferedReader(input);
			String str2 = "";
			s1 = "";
			s2 = "";
			s3 = "";
			s4 = "";
			s5 = "";
			s6 = "";
			s7 = "";
			s8 = "";
			s9 = "";
			s10 = "";
			s11 = "";
			s12 = "";
			s13 = "";
			s14 = "";
			s15 = "";
			s16 = "";
			s17 = "";
			sWriter = "";
			StringBuffer strBuff = new StringBuffer("→");
			while ((str2 = buffReader.readLine()) != null) {
				if (!str2.trim().equals("")) {
					if (str2.contains("mim-number")) {//
						str2 = buffReader.readLine();
						str2 = StringUtil.getSupString(str2);
						s1 = str2.trim();
						System.out.println("\t编号：\t" + s1);
					} else if (str2.contains("preferred-title")) {
						str2 = buffReader.readLine();
						str2 = StringUtil.getSupString(str2);
						s2 = str2.trim();
						System.out.println("\t病名：\t" + str2.trim());
					} else if (str2.contains("approved-gene-symbol text-font")) {
						str2 = StringUtil.getSupString(str2);
						s3 = str2.trim();
						System.out.println("\t基因OMIM号:\t" + str2);
					} else if (str2.contains("致病基因")) {//暂无
						str2 = StringUtil.getSupString(str2);
						s4 = str2.trim();
						System.out.println("\ts4致病基因：\t" + s4);
					} else if (str2
							.contains("gene-map-value text-font lookup value-border")) {
						// Phenotypic Series
						str2 = buffReader.readLine();
						str2 = StringUtil.getSupString(str2);
						s5 = str2.trim();
						System.out.println("\tPhenotypic Series:\t" + str2);
					} else if (str2.contains("别名")) {//待写
						str2 = StringUtil.getSupString(str2);
						s6 = str2.trim(); // 别名暂无
						System.out.println("\t别名：\t" + s6);
					} else if (str2.contains("cytogenetic-location text-font")) {
						// 染色体位置
						str2 = StringUtil.getSupString(str2);
						s7 = str2.trim();
						System.out.println("\t染色体位置：\t" + s7);
					} else if (str2.contains("TEXT暂无")) {//待写
						// TEXT
						str2 = StringUtil.getSupString(str2);
						s8 = str2.trim();
						System.out.println("\tTEXT:\t" + s8);
					} else if (str2.contains("<a name=\"clinicalFeatures\">")) {
						// 9 Clinical Features
						if (!str2.contains("section-text text-font lookup")) {
							str2 = buffReader.readLine();
						}
						if (!str2.contains("section-text text-font lookup")) {
							str2 = buffReader.readLine();
						}
						if (!str2.contains("section-text text-font lookup")) {
							str2 = buffReader.readLine();
						}
						str2 = StringUtil.getSupString(str2);
						s9 = str2.trim();
						System.out.println("\tClinical Features\t" + s9);
					} else if (str2.contains("<a name=\"inheritance\">")) {
						// 10 Inheritance 暂无
						if (!str2.contains("section-text text-font lookup")) {
							str2 = buffReader.readLine();
						}
						str2 = StringUtil.getSupString(str2);
						s10 = str2.trim();
						System.out.println("\tInheritance:\t" + s10);
					} else if (str2.contains("<a name=\"mapping\"></a>")) {
						// 11 Mapping
						str2 = buffReader.readLine();
						str2 = buffReader.readLine();
						str2 = buffReader.readLine();
						str2 = StringUtil.getSupString(str2);
						s11 = str2.trim();
						System.out.println("\tMapping:\t" + s11);
					} else if (str2.contains("str2.contains(\"alternative-title-label text-font\"")) {
						// 12 Population Genetics
						str2 = StringUtil.getSupString(str2);
					} else if (str2.contains("<a name=\"molecularGenetics\"></a>")) {
						// 13 Molecular Genetics
						str2 = buffReader.readLine();
						str2 = buffReader.readLine();
						str2 = buffReader.readLine();
						str2 = StringUtil.getSupString(str2);
						s13 = str2.trim();
						System.out.println("\t13molecular Genetics:\t" + s13);
					} else if (str2.contains("class=\"reference-authors text-font\"")) {
						// 14 REFERENCES 参考文献
						str2 = StringUtil.getSupString(str2.trim());
						strBuff = strBuff.append(str2 + "→");
						str2 = strBuff.toString();
						s14 = str2.trim();
						System.out.println("\t14参考文献\t" + strBuff);
					} else if (str2.contains("name=\"contributors\"")) {
						// 15 contributors
						str2 = buffReader.readLine();
						if (str2.contains("</td>")) {
							str2 = StringUtil.getSupString(str2);
						} else if (!str2.contains("</td>")
								&& str2.contains("</th>")) {
							str2 = StringUtil.getSupString(str2);
						}
						s15 = str2.trim();
						System.out.println("\tcontributors:\t" + s15);
					} else if (str2.contains("name=\"creationDate\"")) {
						// contributors
						str2 = buffReader.readLine();
						str2 = StringUtil.getSupString(str2);
						s16 = str2.trim();
						System.out.println("\tcreateData:\t" + s16);
					} else if (str2.contains("<a name=\"editHistory\">")) {
						// 17 editHistory
						str2 = buffReader.readLine();
						str2 = StringUtil.getSupString(str2);
						s17 = str2.trim();
						System.out.println("\teditHistory：\t" + str2);
					}
				}
			}
			buffReader.close();

			HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(file));
			HSSFSheet sheet = workBook.getSheet("疾病记录");
			HSSFRow titleRow = sheet.getRow(sheet.getLastRowNum());
			titleRow = sheet.createRow(sheet.getLastRowNum() + 1);
			titleRow.createCell(0).setCellValue(s1.trim().toString());
			titleRow.createCell(1).setCellValue(s2.trim().toString());
			titleRow.createCell(2).setCellValue(s3.trim().toString());
			titleRow.createCell(3).setCellValue(s4.trim().toString());
			titleRow.createCell(4).setCellValue(s5.trim().toString());
			titleRow.createCell(5).setCellValue(s6.trim().toString());
			titleRow.createCell(6).setCellValue(s7.trim().toString());
			titleRow.createCell(7).setCellValue(s8.trim().toString());
			titleRow.createCell(8).setCellValue(s9.trim().toString());
			titleRow.createCell(9).setCellValue(s10.trim().toString());
			titleRow.createCell(10).setCellValue(s11.trim().toString());
			titleRow.createCell(11).setCellValue(s12.trim().toString());
			titleRow.createCell(12).setCellValue(s13.trim().toString());
			titleRow.createCell(13).setCellValue(s14.trim().toString());
			titleRow.createCell(14).setCellValue(s15.trim().toString());
			titleRow.createCell(15).setCellValue(s16.trim().toString());
			titleRow.createCell(16).setCellValue(s17.trim().toString());
			//将disease信息存入数据库
			
			Disease disease = new Disease();
			disease.setPhenotypeOmimnumber(s2);
			disease.setDiseaseName(s3);
			//disease.set
	
			Class.forName("com.mysql.jdbc.Driver");
			conn = (Connection) DriverManager.getConnection("jdbc:mysql:///capturedisease", "root","root");
			stat = (Statement) conn.createStatement();
			String sql = "INSERT INTO t_disease(PhenotypeOMIMnumber,diseaseName,GeneOrLocusOMIMnumber,virulenceGene,PhenotypicSeries,"+
							"GeneAlias,chromosomalLocation,`TEXT`,ClinicalFeatures,Inheritance,Mapping,PopulationGenetics,"+
							"MolecularGenetics,`REFERENCES`,`Contributors`,CreationDate,EditHistory"+") " +
							"value ('"+s1.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+s2.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+
							s3.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+s4.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+s5.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+
							"','"+s6.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+s7.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+s8.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+
							"','"+s9.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+s10.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+
							"','"+s11.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+s12.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+s13.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+
							"','"+s14.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+s15.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"','"+s16.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+
							"','"+s17.replaceAll("\"", "\\\"").replaceAll("'", "\\\\'")+"')";
			System.out.println("\tsql语句："+sql);
			
			stat.execute(sql);
			stat.close();
			conn.close();

			FileOutputStream out = new FileOutputStream(file);
			out.flush();
			workBook.write(out);
			out.close();
		} catch (MalformedURLException e) {
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static List<String> getListUrl() {
		return listUrl;
	}

	public static void setListUrl(List<String> listUrl) {
		CaptureServlet.listUrl = listUrl;
	}

	public static String getUrlStr() {
		return urlStr;
	}

	public static void setUrlStr(String urlStr) {
		CaptureServlet.urlStr = urlStr;
	}

	public static int getI() {
		return i;
	}

	public static void setI(int i) {
		CaptureServlet.i = i;
	}

	public static String getS1() {
		return s1;
	}

	public static void setS1(String s1) {
		CaptureServlet.s1 = s1;
	}

	public static String getS2() {
		return s2;
	}

	public static void setS2(String s2) {
		CaptureServlet.s2 = s2;
	}

	public static String getS3() {
		return s3;
	}

	public static void setS3(String s3) {
		CaptureServlet.s3 = s3;
	}

	public static String getS4() {
		return s4;
	}

	public static void setS4(String s4) {
		CaptureServlet.s4 = s4;
	}

	public static String getS5() {
		return s5;
	}

	public static void setS5(String s5) {
		CaptureServlet.s5 = s5;
	}

	public static String getS6() {
		return s6;
	}

	public static void setS6(String s6) {
		CaptureServlet.s6 = s6;
	}

	public static String getS7() {
		return s7;
	}

	public static void setS7(String s7) {
		CaptureServlet.s7 = s7;
	}

	public static String getS8() {
		return s8;
	}

	public static void setS8(String s8) {
		CaptureServlet.s8 = s8;
	}

	public static String getS9() {
		return s9;
	}

	public static void setS9(String s9) {
		CaptureServlet.s9 = s9;
	}

	public static String getS10() {
		return s10;
	}

	public static void setS10(String s10) {
		CaptureServlet.s10 = s10;
	}

	public static String getS11() {
		return s11;
	}

	public static void setS11(String s11) {
		CaptureServlet.s11 = s11;
	}

	public static String getS12() {
		return s12;
	}

	public static void setS12(String s12) {
		CaptureServlet.s12 = s12;
	}

	public static String getS13() {
		return s13;
	}

	public static void setS13(String s13) {
		CaptureServlet.s13 = s13;
	}

	public static String getS14() {
		return s14;
	}

	public static void setS14(String s14) {
		CaptureServlet.s14 = s14;
	}

	public static String getS15() {
		return s15;
	}

	public static void setS15(String s15) {
		CaptureServlet.s15 = s15;
	}

	public static String getS16() {
		return s16;
	}

	public static void setS16(String s16) {
		CaptureServlet.s16 = s16;
	}

	public static String getS17() {
		return s17;
	}

	public static void setS17(String s17) {
		CaptureServlet.s17 = s17;
	}

	public static String getsWriter() {
		return sWriter;
	}

	public static void setsWriter(String sWriter) {
		CaptureServlet.sWriter = sWriter;
	}

	public static String getFirstLink() {
		return firstLink;
	}

	public static void setFirstLink(String firstLink) {
		CaptureServlet.firstLink = firstLink;
	}

}
